package cn.com.jsepc.rep.report;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.servlet.ServletException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import cn.com.jsepc.rep.comm.Common;
import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.demension.CheckLeaf;
import cn.com.jsepc.rep.vo.RepDataVO;
import cn.com.jsepc.rep.vo.RepDemensionVO;

import com.jarp.db.DAO;

/**
 * 导入Excel
 *
 * @author zhaoyuan
 *
 */
public class ImportXls {

    /**
     * 普通用户操作。需要判别时间
     *
     * @return
     */
    public String UserOperate(String url, String repid, String userid) {
        ArrayList<String> logsqls = new ArrayList<String>(); // 系统日志
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        ArrayList<String> alterVersion=new ArrayList<String>();  //用来更改版本号
        String time = sdf.format(date);
        RepDataVO repVo = getRepDemension(repid);
        String res = "";
        DAO dao = new DAO();
        File file = new File(url);
        String[][] result = null;
        try {
            result = getData(file, 0);
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            res="出错,"+e.getMessage();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            res="出错,"+e.getMessage();
        }
        ArrayList<String> list = new ArrayList<String>(); // Excel的数据
        ArrayList dataList = new ArrayList(); // 将Excel数据转化成json格式
        ArrayList demList = new ArrayList(); // 该指标的维度列表
        ArrayList<String> sqls = new ArrayList<String>();  //存放sql语句
        int rowLength = result.length;
        for (int i = 0; i < result[0].length; i++) {
            if (!result[0][i].equals("")) {
                list.add(result[0][i]);
            }
        }
        System.out.println(list);
        for (int i = 1; i < rowLength; i++) {
            Map map = new HashMap();
            for (int j = 0; j < list.size(); j++) {
                map.put(list.get(j), result[i][j]);
            }
            dataList.add(map);
        }
        System.out.println(dataList);
        String sql = "select * from tb_rep_demension t where t.repid=" + repid;
        String demCol = ""; // 事实表维度字段
        String demName = ""; // 事实表维度字段名
        int COUNTSUCCESS = 0;  //执行成功的数量
        int COUNTNULL = 0;    //空数据的数量
        int FORBIT = 0;   //阻止导入的数量
        try {
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                Iterator<Hashtable> iterator = v.iterator();
                while (iterator.hasNext()) {
                    Hashtable hash = iterator.next();
                    demCol += hash.get("FACTDIMCOL") + ",";
                    demName += hash.get("AVAILSNAME") + "编号,";
                }
            }

        } catch (ServletException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            res="出错,"+e.getMessage();
        }
        for (int i = 0; i < dataList.size(); i++) {
            Map map = (HashMap) dataList.get(i);
            String repValue = (String) map.get("当期值");
            String sumValue = (String) map.get("累计值");
            String preval = (String) map.get("上期值");
            String tpreval = (String) map.get("上期累计");
            String trepval = (String) map.get("同期值");
            String tsumval = (String) map.get("同期累计");

            if (repValue.equals("") && sumValue.equals("")) { // 如果当期值和累计值都是空，说明该行数据无效，跳过
                COUNTNULL++;
                continue;
            }
            String[] demColSpilt = demName.split(",");
            String[] factDelColSplit=demCol.split(",");
            String timeColName = this.getTimeName(repid); // 时间维度名称
            String lastTime = this.getLastTime(repid);
            String timeCode = (String) map.get(timeColName);
            String sel_sql="select * from "+repVo.getReffact()+" where 1=1 ";
            String del_sql="delete from "+repVo.getReffact()+" where 1=1 ";
            String sel_alterVersion="UPDATE "+repVo.getReffact()+" SET VERSION=2  where 1=1 ";
            if ((Integer.valueOf(timeCode) >= Integer.valueOf(lastTime))
                    && !timeCode.equals("")) {
                // 进行sql拼接
                String repSql = "insert into "
                        + repVo.getReffact()
                        + " ("
                        + demCol
                        + "REPVAL,SUMVAL,PREVAL,TPREVAL,TREPVAL,TSUMVAL) values(";
                for (String str : demColSpilt) {
                    repSql += (map.get(str).equals("") ? "99999" : map.get(str)) + ",";
                }
                for(int i2=0;i2<demColSpilt.length;i2++){
                   sel_sql+=" and "+factDelColSplit[i2]+"="+(map.get(demColSpilt[i2]).equals("") ? "99999" : map.get(demColSpilt[i2]));
                    del_sql+=" and "+factDelColSplit[i2]+"="+(map.get(demColSpilt[i2]).equals("") ? "99999" : map.get(demColSpilt[i2])); 
                    sel_alterVersion+=" and "+factDelColSplit[i2]+"="+(map.get(demColSpilt[i2]).equals("") ? "99999" : map.get(demColSpilt[i2])); 
                }
               boolean isExsit= this.deleteTheSame(sel_sql, del_sql);
               if(isExsit==true){
            	   alterVersion.add(sel_alterVersion);
               }
                repSql += (map.get("当期值").equals("") ? "NULL" : (String) map.get("当期值")) + ",";
                repSql += (map.get("累计值").equals("") ? "NULL" : map.get("累计值")) + ",";
                repSql += preval.equals("") ? "NULL," : preval + ",";
                repSql += tpreval.equals("") ? "NULL," : tpreval + ",";
                repSql += trepval.equals("") ? "NULL," : trepval + ",";
                repSql += tsumval.equals("") ? "NULL)" : tsumval + ")";
                sqls.add(repSql);
                logsqls.add("INSERT INTO TB_LOG(ID,ROLEID,REPID,TYPE,LOG,TIME)VALUES("
                        + new Common().getSeqStr("SEQ_LOG")
                        + ",'"
                        + userid
                        + "',"
                        + repid
                        + ",1,'用户对指标值进行了添加操作，SQL语句是："
                        + repSql.toString() + "','" + time + "')");
            } else {
                FORBIT++;
                System.out.println("时间检测不符合要求，拒绝该条数据的录入！");
            }
        }
        System.out.println("sqls:"+sqls);
        try {
            if (sqls.size() > 0) {
              //  dao.execCommand(ConstUtil.DBNAME, sqls);
                for(String str:sqls){
                    int count=dao.execCommand(ConstUtil.DBNAME, str);
                    if(count>0){
                        COUNTSUCCESS++;
                    }else{
                        FORBIT++;
                    }
                }
            }
            if(alterVersion.size()>0){
            	for(String str:alterVersion){
            		dao.execCommand(ConstUtil.DBNAME, str);
            	}
            }
            if (logsqls.size() > 0) {
                dao.execCommand(ConstUtil.DBNAME, logsqls);
            }
            res = "成功导入的行数：" + COUNTSUCCESS + "行；\n"
                    + "数据为空的行数：" + COUNTNULL + "行；\n"
                    + "被系统阻止的行数：" + FORBIT + "行；\n请仔细核对导入的数据量是否准确，如有疑问请联系技术人员协助解决";
            String getReffact = "SELECT t.REFFACT FROM tb_report t WHERE t.REPID=" + repid;
            String facttable = dao.getDataValue(ConstUtil.DBNAME, getReffact);
            //	new GetHandmadeReport().getHandmade(facttable);
        } catch (ServletException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            res="出错,"+e.getMessage();
        }
        return res;
    }

    /**
     * 管理员操作，可以导入历史数据
     *
     * @return
     */
    public String AdminOperate(String url, String repid, String userid) {
        ArrayList<String> logsqls = new ArrayList<String>(); // 系统日志
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String time = sdf.format(date);
        RepDataVO repVo = getRepDemension(repid);
        String res = "";
        DAO dao = new DAO();
        File file = new File(url);
        String[][] result = null;
        try {
            result = getData(file, 0);
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            res="出错,"+e.getMessage();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            res="出错,"+e.getMessage();
        }

        ArrayList<String> list = new ArrayList<String>(); // Excel的数据
        ArrayList dataList = new ArrayList(); // 将Excel数据转化成json格式
        ArrayList demList = new ArrayList(); // 该指标的维度列表
        ArrayList<String> sqls = new ArrayList<String>();  //存放sql语句
        int rowLength = result.length;
        for (int i = 0; i < result[0].length; i++) {
            if (!result[0][i].equals("")) {
                list.add(result[0][i]);
            }
        }
        for (int i = 1; i < rowLength; i++) {
            Map map = new HashMap();
            for (int j = 0; j < list.size(); j++) {
                map.put(list.get(j), result[i][j]);
            }
            dataList.add(map);
        }
        String sql = "select * from tb_rep_demension t where t.repid=" + repid;
        String demCol = ""; // 事实表维度字段
        String demName = ""; // 事实表维度字段名
        int COUNTSUCCESS = 0;  //执行成功的数量
        int COUNTNULL = 0;    //空数据的数量
        int FORBIT = 0;   //阻止导入的数量
        try {
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                Iterator<Hashtable> iterator = v.iterator();
                while (iterator.hasNext()) {
                    Hashtable hash = iterator.next();
                    demCol += hash.get("FACTDIMCOL") + ",";
                    demName += hash.get("AVAILSNAME") + "编号,";
                }
            }

        } catch (ServletException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        for (int i = 0; i < dataList.size(); i++) {
            Map map = (HashMap) dataList.get(i);
            String repValue = (String) map.get("当期值");
            String sumValue = (String) map.get("累计值");
            String preval = (String) map.get("上期值");
            String tpreval = (String) map.get("上期累计");
            String trepval = (String) map.get("同期值");
            String tsumval = (String) map.get("同期累计");
            if (repValue.equals("") && sumValue.equals("")) { // 如果当期值和累计值都是空，说明该行数据无效，跳过
                COUNTNULL++;
                continue;
            }
            String[] demColSpilt = demName.split(",");
            String timeColName = this.getTimeName(repid); // 时间维度名称		
            COUNTSUCCESS++;
            // 进行sql拼接
            String repSql = "insert into "
                    + repVo.getReffact()
                    + " ("
                    + demCol
                    + "REPVAL,SUMVAL,PREVAL,TPREVAL,TREPVAL,TSUMVAL) values(";
            for (String str : demColSpilt) {
                repSql += (map.get(str).equals("") ? "99999," : map.get(str)) + ",";
            }
            repSql += (map.get("当期值").equals("") ? "NULL," : (String) map.get("当期值")) + ",";
            repSql += (map.get("累计值").equals("") ? "NULL," : map.get("累计值")) + ",";
            repSql += preval.equals("") ? "NULL," : preval + ",";
            repSql += tpreval.equals("") ? "NULL," : tpreval + ",";
            repSql += trepval.equals("") ? "NULL," : trepval + ",";
            repSql += tsumval.equals("") ? "NULL" : tsumval + ")";
            sqls.add(repSql);
            logsqls.add("INSERT INTO TB_LOG(ID,ROLEID,REPID,TYPE,LOG,TIME)VALUES("
                    + new Common().getSeqStr("SEQ_LOG")
                    + ",'"
                    + userid
                    + "',"
                    + repid
                    + ",1,'用户对指标值进行了添加操作，SQL语句是："
                    + repSql.toString() + "','" + time + "')");
        }
        try {
            if (sqls.size() > 0) {
                dao.execCommand(ConstUtil.DBNAME, sqls);
            }
            if (logsqls.size() > 0) {
                dao.execCommand(ConstUtil.DBNAME, logsqls);
            }
            res = "成功导入的行数：" + COUNTSUCCESS + "行；\n"
                    + "数据为空的行数：" + COUNTNULL + "行；\n"
                    + "具体导入的数据可以查询系统操作日志";
            String getReffact = "SELECT t.REFFACT FROM tb_report t WHERE t.REPID=" + repid;
            String facttable = dao.getDataValue(ConstUtil.DBNAME, getReffact);
            //	new GetHandmadeReport().getHandmade(facttable);
        } catch (ServletException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            res="出错，"+e.getMessage();
        }
        return res;
    }

    /**
     * 获取上期时间
     *
     * @param repid
     * @return
     */
    public String getLastTime(String repid) {
        String lastTime = "";
        String timeCol = this.getTimeCol(repid); // 获取时间维度
        if (timeCol == null || timeCol.equals("")) {
            System.out.println("该指标没有关联时间维度！");
            return "";
        }
        if (timeCol.equals("YEARID")) { // 时间维度是年
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.YEAR, -1);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            lastTime = sdf.format(date);
        } else if (timeCol.equals("YEARMONTHID")) { // 时间维度是年月
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.MONTH, -1);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
            lastTime = sdf.format(date);
        } else if (timeCol.equals("YEARMONTHDAYID")) { // 时间维度是年月日
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.DAY_OF_YEAR, -1);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            lastTime = sdf.format(date);
        } else if (timeCol.equals("YEARQUERTERID")) { // 时间维度是季度
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.MONTH, -3);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            String year = sdf.format(date);
            sdf = new SimpleDateFormat("MM");
            String month = sdf.format(date);
            int mon = Integer.valueOf(month);
            int qua = mon % 3 == 0 ? mon / 3 : mon / 3 + 1;
            lastTime = year + "0" + qua;
        } else if (timeCol.equals("HALFYEARID")) { // 时间维度是半年
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.MONTH, -6);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            String year = sdf.format(date);
            sdf = new SimpleDateFormat("MM");
            String month = sdf.format(date);
            int mon = Integer.valueOf(month);
            int qua = mon % 6 == 0 ? mon / 6 : mon / 6 + 1;
            lastTime = year + "0" + qua;
        } else if (timeCol.equals("YEARMONTHWEEKID")) { // 时间维度是周
            System.out.println(this.getTimeCol("297"));
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.WEEK_OF_YEAR, -1);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyww");
            lastTime = sdf.format(date);
        }
        return lastTime;
    }

    /**
     * 获取指标的时间维度字段
     *
     * @param repid
     * @return
     */
    public String getTimeCol(String repid) {
        String res = "";
        RepDataVO repVo = getRepDemension(repid);
        if (repVo != null) {
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            for (int j = 0; j < repDemLst.size(); j++) {
                RepDemensionVO repDemVO = repDemLst.get(j);
                String facdimcol = repDemVO.getFactdimcol();
                String dimtype = repDemVO.getDemtype(); // 1单位维度 2时间维度 3其他维度
                if (dimtype.equals("2")) {
                    res = facdimcol;
                }
            }
        }
        return res;
    }

    /**
     * 获取时间维度名称
     *
     * @param repid
     * @return
     */
    public String getTimeName(String repid) {
        String res = "";
        RepDataVO repVo = getRepDemension(repid);
        if (repVo != null) {
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            for (int j = 0; j < repDemLst.size(); j++) {
                RepDemensionVO repDemVO = repDemLst.get(j);
                String facdimcol = repDemVO.getDemname() + "编号";
                String dimtype = repDemVO.getDemtype(); // 1单位维度 2时间维度 3其他维度
                if (dimtype.equals("2")) {
                    res = facdimcol;
                }
            }
        }
        return res;
    }

    /**
     * 获取指标VO
     *
     * @param repid
     * @return
     */
    public RepDataVO getRepDemension(String repid) {
        RepDataVO repVO = new RepDataVO();
        DAO dao = new DAO();
        try {
            String sql = "select * from ( select * from tb_report where repid="
                    + repid
                    + " and ISVALID=1 ) a"
                    + " left join tb_rep_demension b on b.repid=a.repid"
                    + " left join tb_demension c on c.demid=b.dimid order by b.dimorder";
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                ArrayList<RepDemensionVO> repDemLst = new ArrayList<RepDemensionVO>();
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    RepDemensionVO repDemVO = new RepDemensionVO();
                    repDemVO.setId((String) hashRows.get("ID"));
                    repDemVO.setRepid((String) hashRows.get("REPID"));
                    repDemVO.setDimid((String) hashRows.get("DIMID"));
                    repDemVO.setHirecdesc((String) hashRows.get("HIRECDESC"));
                    repDemVO.setDemname((String) hashRows.get("DEMNAME"));
                    repDemVO.setFactdimcol(((String) hashRows.get("FACDIMCOL"))
                            .toUpperCase());
                    repDemVO.setAvailsname((String) hashRows.get("AVAILSNAME"));
                    repDemVO.setDimorder((String) hashRows.get("DIMORDER"));
                    repDemVO.setDemcode((String) hashRows.get("DEMCODE"));
                    repDemVO.setDimtable((String) hashRows.get("DIMTABLE"));
                    repDemVO.setIdcol(((String) hashRows.get("IDCOL"))
                            .toUpperCase());
                    repDemVO.setNamecol(((String) hashRows.get("NAMECOL"))
                            .toUpperCase());
                    repDemVO.setParentcol(((String) hashRows.get("PARENTCOL"))
                            .toUpperCase());
                    repDemVO.setCodecol(((String) hashRows.get("CODECOL"))
                            .toUpperCase());
                    repDemVO.setDemtype((String) hashRows.get("DIMTYPE"));
                    repDemVO.setIsvalid((String) hashRows.get("ISVALID"));
                    repDemVO.setLevelcol((String) hashRows.get("LEVELCOL"));
                    repDemLst.add(repDemVO);
                    if (i == v.size() - 1) {
                        repVO.setRepid((String) hashRows.get("REPID"));
                        repVO.setRepcode((String) hashRows.get("REPCODE"));
                        repVO.setRepname((String) hashRows.get("REPNAME"));
                        repVO.setCataid((String) hashRows.get("CATAID"));
                        repVO.setRepdesc((String) hashRows.get("REPDESC"));
                        repVO.setDesc((String) hashRows.get("CALDESC"));
                        repVO.setDataprec((String) hashRows.get("DATAPREC"));
                        repVO.setDataunit((String) hashRows.get("DATAUNIT"));
                        repVO
                                .setDepartment((String) hashRows
                                .get("DEPARTMENT"));
                        repVO
                                .setSourcetype((String) hashRows
                                .get("SOURCETYPE"));
                        repVO.setDatatype((String) hashRows.get("DATATYPE"));
                        repVO
                                .setUpdatetime((String) hashRows
                                .get("UPDATETIME"));
                        repVO.setReffact((String) hashRows.get("REFFACT"));
                        repVO.setRepvaluecol(((String) hashRows
                                .get("REPVALUECOL")).toUpperCase());
                        repVO.setIssum((String) hashRows.get("ISSUM"));
                        repVO.setIsvalid((String) hashRows.get("ISVALID"));
                        repVO.setRepDemLst(repDemLst);
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("FillDataDAO getFillGrid Exception.");
        }
        return repVO;
    }

    public String getDimCodeByID(String dimtablename, String dimidcol,
            String dimcodecol, String dimidval) {
        String sret = "";
        try {
            DAO dao = new DAO();
            String sql = "select " + dimcodecol + " from " + dimtablename
                    + " where " + dimidcol + "=" + dimidval + "";
            sret = dao.getDataValue(ConstUtil.DBNAMEODS, sql);
        } catch (ServletException ex) {
            Logger.getLogger(CheckLeaf.class.getName()).log(Level.SEVERE, null,
                    ex);
        }
        return sret;
    }

    public String getDimNamebyId(String dimtablename, String dimnamecol,
            String dimidcol, String dimidval) {
        String sret = "";
        try {
            DAO dao = new DAO();
            String sql = "select " + dimnamecol + " from " + dimtablename
                    + " where " + dimidcol + "=" + dimidval + "";
            sret = dao.getDataValue(ConstUtil.DBNAMEODS, sql);
        } catch (ServletException ex) {
            Logger.getLogger(CheckLeaf.class.getName()).log(Level.SEVERE, null,
                    ex);
        }
        return sret;
    }

    /**
     *
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     *
     * @param file 读取数据的源Excel
     *
     * @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
     *
     * @return 读出的Excel中数据的内容
     *
     * @throws FileNotFoundException
     *
     * @throws IOException
     *
     */
    public static String[][] getData(File file, int ignoreRows)
            throws FileNotFoundException, IOException {

        List<String[]> result = new ArrayList<String[]>();

        int rowSize = 0;

        BufferedInputStream in = new BufferedInputStream(new FileInputStream(
                file));

        // 打开HSSFWorkbook

        POIFSFileSystem fs = new POIFSFileSystem(in);

        HSSFWorkbook wb = new HSSFWorkbook(fs);

        HSSFCell cell = null;

        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {

            HSSFSheet st = wb.getSheetAt(sheetIndex);

            // 第一行为标题，不取

            for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {

                HSSFRow row = st.getRow(rowIndex);

                if (row == null) {

                    continue;

                }

                int tempRowSize = row.getLastCellNum() + 1;

                if (tempRowSize > rowSize) {

                    rowSize = tempRowSize;

                }

                String[] values = new String[rowSize];

                Arrays.fill(values, "");

                boolean hasValue = false;

                for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {

                    String value = "";

                    cell = row.getCell(columnIndex);

                    if (cell != null) {

                        // 注意：一定要设成这个，否则可能会出现乱码

                        cell.setEncoding(HSSFCell.ENCODING_UTF_16);

                        switch (cell.getCellType()) {

                            case HSSFCell.CELL_TYPE_STRING:

                                value = cell.getStringCellValue();

                                break;

                            case HSSFCell.CELL_TYPE_NUMERIC:

                                if (HSSFDateUtil.isCellDateFormatted(cell)) {

                                    Date date = cell.getDateCellValue();

                                    if (date != null) {

                                        value = new SimpleDateFormat("yyyy-MM-dd")
                                                .format(date);

                                    } else {

                                        value = "";

                                    }

                                } else {

                                    value = new DecimalFormat("0.00").format(cell
                                            .getNumericCellValue());

                                }

                                break;

                            case HSSFCell.CELL_TYPE_FORMULA:

                                // 导入时如果为公式生成的数据则无值

                                if (!cell.getStringCellValue().equals("")) {

                                    value = cell.getStringCellValue();

                                } else {

                                    value = cell.getNumericCellValue() + "";

                                }

                                break;

                            case HSSFCell.CELL_TYPE_BLANK:

                                break;

                            case HSSFCell.CELL_TYPE_ERROR:

                                value = "";

                                break;

                            case HSSFCell.CELL_TYPE_BOOLEAN:

                                value = (cell.getBooleanCellValue() == true ? "Y"
                                        : "N");

                                break;

                            default:

                                value = "";

                        }

                    }
                    System.out.println(value);
                    values[columnIndex] = rightTrim(value);

                    hasValue = true;

                }



                if (hasValue) {

                    result.add(values);

                }

            }

        }

        in.close();

        String[][] returnArray = new String[result.size()][rowSize];

        for (int i = 0; i < returnArray.length; i++) {

            returnArray[i] = (String[]) result.get(i);

        }

        return returnArray;

    }

    /**
     *
     * 去掉字符串右边的空格
     *
     * @param str 要处理的字符串
     *
     * @return 处理后的字符串
     *
     */
    public static String rightTrim(String str) {

        if (str == null) {

            return "";

        }

        int length = str.length();

        for (int i = length - 1; i >= 0; i--) {

            if (str.charAt(i) != 0x20) {

                break;

            }

            length--;

        }
        return str.substring(0, length);
    }
    
    /**
     * 删除事实表里重复的值（一般用于用户导入重复的记录，需要覆盖)
     * @param sql 
     */
    public boolean deleteTheSame(String sel_sql,String del_sql){
        DAO dao=new DAO();
        boolean b=false;
        try {
            int count=dao.getDataSetCount(ConstUtil.DBNAME, sel_sql);
            if(count>0){  //该条记录已经存在
            	b=true;
                dao.execCommand(ConstUtil.DBNAME, del_sql);
            }
        } catch (ServletException ex) {
            Logger.getLogger(ImportXls.class.getName()).log(Level.SEVERE, null, ex);
        }
        return b;
    }
}